<?php
namespace Versal\AdminBundle\Dao;
use Versal\SecurityBundle\Entity\Usuario as Usuario;
use Versal\SecurityBundle\Entity\Password as Password;
use Versal\SecurityBundle\Entity\Perfil as Perfil;
use Doctrine\ORM\Query\ResultSetMapping;


/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of UserDao
 *
 * @author Jorge
 */
class UserDao {
    //put your code here
    protected $conexion;
    
    public function __construct($conexion){
        $this->conexion = $conexion;
    }
	
    public function getConexion(){
        return $this->conexion;
    }
    
    public function getUsuario(\Usuario $usuario) {
        
        $rsm = new ResultSetMapping;
        $rsm->addEntityResult('Versal\SecurityBundle\Entity\Usuario', 'u');
        $rsm->addFieldResult('u', 'login_id', 'userId'); // ($alias, $columnName, $fieldName)
        $rsm->addFieldResult('u', 'login_username', 'username');
        $rsm->addFieldResult('u', 'login_nombre', 'nombre');
        $rsm->addFieldResult('u', 'login_apepa', 'apePaterno');
        $rsm->addFieldResult('u', 'login_apema', 'apeMaterno');
        $rsm->addFieldResult('u', 'login_fecultimoingr', 'fechaUltimoIngreso');
        $rsm->addFieldResult('u', 'login_fecactualizacion', 'fechaActualizacion');
        $rsm->addFieldResult('u', 'login_creacion', 'fechaCreacion');
        $rsm->addFieldResult('u', 'login_email', 'email');
        $rsm->addFieldResult('u', 'login_dircalle', 'dirCalle');
        $rsm->addFieldResult('u', 'login_dirnumero', 'dirNumero');
        $rsm->addFieldResult('u', 'login_dircomuna', 'dirComuna');
        $sql = 'call obtenerUsuario (?, @errIdn, @errGlosa)';     
        $query = $this->conexion->createNativeQuery($sql, $rsm);
        $query->setParameter(1, $usuario->getUsername());
        $usuario = $query->getResult();
        return $usuario;
    }
    
    public function getPassword(\Usuario $usuario){
        $conn = $this->getConexion();
        $result = $conn->fetchAssoc('select p.pwd_id, 
        			            p.login_id,
                                            p.pwd_password,
                                            p.pwd_fechacrea, 
                                            p.pwd_fechanull,
                                            p.pwd_indnuevo                                             
                                      from  login_password p                                      
                                      where p.login_id = ? and
                                       p.pwd_fechacrea = (select MAX(b.pwd_fechacrea) 
                                                          from login_password b 
                                                          where b.login_id = p.login_id)', 
                                       array($usuario->getUserId()));
        //$outParam = $conn->fetchAssoc('select @errIdn as errIdn, @errGls as errGls');
        if(count($result) > 0){            
            $password = new Password();
            $password->setPasswordId($result['pwd_id']);
            $password->setPassword($result['pwd_password']);
            $password->setFechaCreacion($result['pwd_fechacrea']);
            $password->setFechaNull($result['pwd_fechanull']);
            $password->setIndNuevo($result['pwd_indnuevo']);
            return $password;
            
        } else{
            return false;
        } 
    }
    
    public function getPerfilesUsuario(Usuario $usuario){
        $conn = $this->getConexion();
        $result = $conn->fetchAll('select perfil_id, 
                                          login_id, 
                                          apl_id, 
                                          rol_id 
                                    from  login_perfil 
                                    where login_id = ? ', 
                                    array($usuario->getUserId()));        
        //$outParam = $conn->fetchAssoc('select @errIdn as errIdn, @errGls as errGls');
        if(count($result) > 0){    
            $perfiles = array();
            foreach($result as $row){
                $perfil = new Perfil();
                $perfil->setPerfilId($row['perfil_id']);
                $perfil->setPerfil($row['rol_id']);
                $perfil->setIdApp($row['apl_id']);
                $perfiles[] = $perfil;
            }
            return $perfiles;
        } else{
            return false;
        }
        
    }
    
    public function listarUsuarios() {               
        $rsm = new ResultSetMapping;
        $rsm->addEntityResult('Versal\SecurityBundle\Entity\Usuario', 'u');
        $rsm->addFieldResult('u', 'login_id', 'userId'); // ($alias, $columnName, $fieldName)
        $rsm->addFieldResult('u', 'login_username', 'username');
        $rsm->addFieldResult('u', 'login_nombre', 'nombre');
        $rsm->addFieldResult('u', 'login_apepa', 'apePaterno');
        $rsm->addFieldResult('u', 'login_apema', 'apeMaterno');
        $rsm->addFieldResult('u', 'login_fecultimoingr', 'fechaUltimoIngreso');
        $rsm->addFieldResult('u', 'login_fecactualizacion', 'fechaActualizacion');
        $rsm->addFieldResult('u', 'login_creacion', 'fechaCreacion');
        $rsm->addFieldResult('u', 'login_email', 'email');
        $rsm->addFieldResult('u', 'login_dircalle', 'dirCalle');
        $rsm->addFieldResult('u', 'login_dirnumero', 'dirNumero');
        $rsm->addFieldResult('u', 'login_dircomuna', 'dirComuna');
        $sql = 'Call buscarUsuario(\'\', @errIdn, @errGls);';
        $query = $this->conexion->createNativeQuery($sql, $rsm);
        //$query->setParameter(1, 'romanb');

        $users = $query->getResult();
        return $users;

    }
    
    public function buscarUsuario(\Usuario $usuario) {                 
                
        $rsm = new ResultSetMapping;
        $rsm->addEntityResult('Versal\SecurityBundle\Entity\Usuario', 'u');
        $rsm->addFieldResult('u', 'login_id', 'userId'); // ($alias, $columnName, $fieldName)
        $rsm->addFieldResult('u', 'login_username', 'username');
        $rsm->addFieldResult('u', 'login_nombre', 'nombre');
        $rsm->addFieldResult('u', 'login_apepa', 'apePaterno');
        $rsm->addFieldResult('u', 'login_apema', 'apeMaterno');
        $rsm->addFieldResult('u', 'login_fecultimoingr', 'fechaUltimoIngreso');
        $rsm->addFieldResult('u', 'login_fecactualizacion', 'fechaActualizacion');
        $rsm->addFieldResult('u', 'login_creacion', 'fechaCreacion');
        $rsm->addFieldResult('u', 'login_email', 'email');
        $rsm->addFieldResult('u', 'login_dircalle', 'dirCalle');
        $rsm->addFieldResult('u', 'login_dirnumero', 'dirNumero');
        $rsm->addFieldResult('u', 'login_dircomuna', 'dirComuna');
        $sql = 'Call buscarUsuario(?, @errIdn, @errGls);';
        $query = $this->conexion->createNativeQuery($sql, $rsm);
        $query->setParameter(1, $usuario->getUsername());
        //$query->setParameter(2, '%' . $usuario->getNombre() . '%');
        $users = $query->getResult();        
        return $users;                           
    }                        
    
}
